package com.sy.dao.impl;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import com.sy.dao.NewsDao;
import com.sy.sql.DataBaseConnection;
import com.sy.util.StringUtil;
import com.sy.vo.News;

public class NewsDaoImpl implements NewsDao {
//添加新闻
	
	public int add(News news){
		int result=0;
		PreparedStatement pstmt	= null ;
		String sql				= null ;
		DataBaseConnection dbc= new DataBaseConnection() ;
		sql = "insert into struts2new(name,email,title,content,date) values(?,?,?,?,?)";
		try
		{			
			pstmt = dbc.getSql(sql);
			pstmt.setString(1, StringUtil.filterHtml(news.getName()));
			pstmt.setString(2, StringUtil.filterHtml(news.getEmail()));
			pstmt.setString(3, StringUtil.filterHtml(news.getTitle()));
			pstmt.setString(4,news.getContent());

			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
			pstmt.setString(5, sdf.format(new java.util.Date()));
			result = pstmt.executeUpdate();
			pstmt.close() ;
		}
		catch(Exception e)
		{
			System.out.println(e) ;
		}
		finally
		{
			dbc.close() ;
		}
		return result;
	}
//获取单个新闻
	public News getNews(Integer id){
		News news=new News();
		PreparedStatement pstmt	= null ;
		String sql				= null ;
		ResultSet rs			= null ;
		DataBaseConnection dbc= new DataBaseConnection() ;

		sql = "SELECT * FROM struts2new WHERE id=?";
		try
		{			
			pstmt = dbc.getSql(sql);
			
			pstmt.setInt(1,id);
			rs = pstmt.executeQuery() ;
			while(rs.next())
			{
				news.setId(rs.getInt("id"));
				news.setName(rs.getString("name"));
				news.setTitle(rs.getString("title"));
				news.setDate(rs.getString("date"));
				news.setEmail(rs.getString("email"));
				news.setContent(rs.getString("content"));
			}
			rs.close() ;
			pstmt.close() ;
		}
		catch(Exception e)
		{
			System.out.println(e) ;
		}
		finally
		{
			dbc.close();
		}
		return news;
	}
//获取分页新闻列表
	@SuppressWarnings("unchecked")
	public List<News> queryByPage(int i,int pageSize){
		List<News> newss=new ArrayList();
		PreparedStatement pstmt	= null ;
		String sql				= null ;
		DataBaseConnection dbc= new DataBaseConnection() ;
		ResultSet rs			= null ;
		sql = "select * from struts2new order by id asc limit " + i + "," + pageSize;
		try
		{		
			
			pstmt = dbc.getSql(sql);
			
			rs = pstmt.executeQuery() ;
			while(rs.next())
			{
				News news=new News();
				news.setId(rs.getInt("id"));
				news.setName(rs.getString("name"));
				news.setTitle(rs.getString("title"));
				news.setDate(rs.getString("date"));
				news.setEmail(rs.getString("email"));
				news.setContent(rs.getString("content"));
				i++;
				newss.add(news);
			}
			rs.close() ;
			pstmt.close() ;
		}
		catch(Exception e)
		{
			System.out.println(e) ;
		}
		finally
		{
			dbc.close();
		}
		return newss;
	}
//获取新闻列表
	@SuppressWarnings("unchecked")
//	public List<News> getNews(){
//		List<News> newss=new ArrayList();
//		
//		PreparedStatement pstmt	= null ;
//		String sql				= null ;
//		ResultSet rs			= null ;
//		DataBaseConnection dbc	= null ;
//		dbc = new DataBaseConnection() ;
//
//		sql = "select * from struts2new order by id desc";
//		try
//		{			
//			pstmt = dbc.getConnection().prepareStatement(sql);
//			rs = pstmt.executeQuery() ;
//			while(rs.next())
//			{
//				News news=new News();
//				news.setId(rs.getInt("id"));
//				news.setName(rs.getString("name"));
//				news.setTitle(rs.getString("title"));
//				news.setDate(rs.getString("date"));
//				news.setEmail(rs.getString("email"));
//				news.setContent(rs.getString("content"));
//				newss.add(news);
//			}
//			rs.close() ;
//			pstmt.close() ;
//		}
//		catch(Exception e)
//		{
//			System.out.println(e) ;
//		}
//		finally
//		{
//			dbc.close();
//		}
//		return newss;
//	}
//修改新闻
	public int update(News news){
		int result=0;
		PreparedStatement pstmt	= null ;
		String sql				= null ;
		DataBaseConnection dbc= new DataBaseConnection() ;
		sql = "update struts2new set name=?,email=?,title=?,content=?,date=? where id=?";
		try
		{			
			pstmt = dbc.getSql(sql);
			pstmt.setInt(6,news.getId());
			pstmt.setString(1, StringUtil.filterHtml(news.getName()));
			pstmt.setString(2, StringUtil.filterHtml(news.getEmail()));
			pstmt.setString(3, StringUtil.filterHtml(news.getTitle()));
			pstmt.setString(4,news.getContent());

			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
			pstmt.setString(5, sdf.format(new java.util.Date()));
			result = pstmt.executeUpdate();
			pstmt.close() ;
		}
		catch(Exception e)
		{
			System.out.println(e) ;
		}
		finally
		{
			dbc.close() ;
		}
		return result;
	}
//删除新闻
	public void delete(Integer id){
		PreparedStatement pstmt	= null ;
		String sql				= null ;
		DataBaseConnection dbc= new DataBaseConnection() ;
		sql = "delete from struts2new WHERE id=?";
		try
		{			
			pstmt = dbc.getSql(sql);
			pstmt.setInt(1,id);
			pstmt.executeUpdate();//这句必须加
			pstmt.close();
		}
		catch(Exception e)
		{
			System.out.println(e) ;
		}
		finally
		{
			dbc.close();
		}
	}
//查询总行数
	public int count() {
		int intRowCount = 0;//总行数
		PreparedStatement pstmt	= null ;
		String sql				= null ;
		ResultSet rs			= null ;
		DataBaseConnection dbc= new DataBaseConnection() ;
		sql = "select count(id) from struts2new order by id asc";
		try
		{			
			pstmt = dbc.getSql(sql);
			rs = pstmt.executeQuery();
			rs.next();//游标指向第一行
			intRowCount=rs.getInt(1);//取得总行数
			rs.close() ;
			pstmt.close() ;
		}
		catch(Exception e)
		{
			System.out.println(e) ;
		}
		finally
		{
			dbc.close();
		}
		return intRowCount;
	}
}
